var express = require('express');
var Msdb = require("../../../db/Mysqldb.js")
var router = express.Router()   //可使用 express.Router 类创建模块化、可挂载的路由句柄

var db = new Msdb()
var getTime=()=>{
    return new Date(new Date().getTime()+8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');//获取系统时间并转为数据库接受的格式
}

router.post("/getCustomers", async function(req, res) {
    var result = {};
    var data = req.body;
    try {
        // 基础SQL语句
        let query = "SELECT * FROM customer";
        let countQuery = "SELECT COUNT(*) AS total FROM customer";
        let params = [];
        let hasWhere = false;
        
        // 优先处理ID查询（精确匹配）
        if (data.id && data.id.toString().trim() !== "") {
            query += " WHERE id = ?";
            countQuery += " WHERE id = ?";
            params.push(data.id.toString().trim());
            hasWhere = true;
        }
        // 处理名称查询（模糊匹配）
        else if (data.name && data.name.trim() !== "") {
            query += " WHERE name LIKE ?";
            countQuery += " WHERE name LIKE ?";
            params.push(`%${data.name.trim()}%`); // 添加模糊查询的通配符
            hasWhere = true;
        }
        
        // 添加分页条件（如果不是只查询单个ID）
        // 当按ID查询时，默认只返回一个结果，不需要分页
        if (!data.id || data.includePagination) {
            query += hasWhere ? " LIMIT ? OFFSET ?" : " LIMIT ? OFFSET ?";
            params.push(data.pageSize || 10, data.offset || 0);
        }
        
        // 执行查询
        var customers = await db.query(query, params);
        // 执行总数查询（注意参数处理）
        const countParams = data.id ? [params[0]] : (data.name ? [params[0]] : []);
        const [countResult] = await db.query(countQuery, countParams);
        
        result = {
            code: 200,
            msg: "查询成功",
            data: data.id ? (customers.length > 0 ? customers[0] : null) : customers,
            total: countResult.total
        };
    } catch (error) {
        result = {
            code: 500,
            msg: "查询失败: " + error.message,
            data: null,
            total: 0
        };
    }
    res.json(result);
});

router.post("/add", async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    var result = {};
    var data = req.body;
    
    try {
        // 验证必要参数
        if (!data.name || data.name.trim() === "") {
            result = {
                code: 400,
                msg: "客户名称不能为空"
            };
            res.json(result)
            return;
        }
        if (!data.phone || data.phone.trim() === "") {
            result = {
                code: 400,
                msg: "负责人联系方式不能为空"
            };
            res.json(result)
            return;
        }
        
        // 获取当前登录用户信息
        const { user_id } = req.auth;
        if (!user_id) {
            result = {
                code: 401,
                msg: "未获取到用户信息，请重新登录"
            };
            res.json(result)
            return;
        }
        
        // 检查客户名称是否已存在
        const nameResult = await connection.execute(
            `SELECT id FROM customer WHERE name = ?`,
            [data.name.trim()]
        );
        const existingCustomer = nameResult[0];
        
        if (existingCustomer.length !== 0) {
            // 客户名称已存在
            result = {
                code: 201,
                msg: "客户名称已存在，请修改名称"
            };
            res.json(result)
            return;
        }
        
        // 准备插入数据
        const customerName = data.name.trim();
        const phone = data.phone.trim();
        const createTime = getTime(); // 使用与角色新增相同的时间获取方法
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 执行数据库插入
        const insertResult = await connection.execute(
            `INSERT INTO customer 
             (name,phone, insert_user_id, create_time) 
             VALUES (?, ?, ?, ?)`,
            [customerName, phone, user_id, createTime]
        );
        
        // 准备操作日志
        const customerId = insertResult[0].insertId;
        const sql_words = `ID为${user_id}的用户新增了ID为${customerId}的客户`;
        
        // 插入操作日志
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?);`,
            [user_id, sql_words, createTime]
        );
        
        // 提交事务
        await db.commit(connection);
        
        // 返回成功结果
        result = {
            code: 200,
            msg: "客户新增成功"
        };
        res.json(result)
    } catch (error) {
        // 回滚事务
        await db.rollback(connection);
        result = {
            code: 500,
            msg: "服务器错误，新增客户失败"
        };
        res.json(result)
    } finally {
        // 释放连接
        connection.release();
    }
});

// 更新客户信息接口
router.post("/update", async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    var result = {};
    var data = req.body;
    
    try {
        // 验证必要参数
        if (!data.id) {
            result = {
                code: 400,
                msg: "客户ID不能为空"
            };
            res.json(result);
            return;
        }
        if (!data.name || data.name.trim() === "") {
            result = {
                code: 400,
                msg: "客户名称不能为空"
            };
            res.json(result);
            return;
        }
        if (!data.phone || data.phone.trim() === "") {
            result = {
                code: 400,
                msg: "负责人联系方式不能为空"
            };
            res.json(result);
            return;
        }
        
        // 获取当前登录用户信息
        const { user_id } = req.auth;
        if (!user_id) {
            result = {
                code: 401,
                msg: "未获取到用户信息，请重新登录"
            };
            res.json(result);
            return;
        }
        
        // 检查客户是否存在
        const [existingCustomer] = await connection.execute(
            `SELECT id, name FROM customer WHERE id = ?`,
            [data.id]
        );
        
        if (existingCustomer.length === 0) {
            result = {
                code: 404,
                msg: "未找到指定客户"
            };
            res.json(result);
            return;
        }
        
        // 检查客户名称是否已被其他客户使用
        const [nameCheck] = await connection.execute(
            `SELECT id FROM customer WHERE name = ? AND id != ?`,
            [data.name.trim(), data.id]
        );
        
        if (nameCheck.length > 0) {
            result = {
                code: 201,
                msg: "客户名称已存在，请修改名称"
            };
            res.json(result);
            return;
        }
        
        // 准备更新数据
        const customerName = data.name.trim();
        const phone = data.phone.trim();
        const updateTime = getTime(); // 使用与新增相同的时间获取方法
        const originalName = existingCustomer[0].name; // 记录原始名称用于日志
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 执行更新操作
        await connection.execute(
            `UPDATE customer 
             SET name = ?, phone = ?, update_user_id = ?, update_time = ? 
             WHERE id = ?`,
            [customerName, phone, user_id, updateTime, data.id]
        );
        
        // 准备操作日志（记录更新前后的变化）
        const sql_words = `ID为${user_id}的用户将ID为${data.id}的客户名称从"${originalName}"修改为"${customerName}"，联系方式修改为"${phone}"`;
        
        // 插入操作日志
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?);`,
            [user_id, sql_words, updateTime]
        );
        
        // 提交事务
        await db.commit(connection);
        
        // 返回成功结果
        result = {
            code: 200,
            msg: "客户信息更新成功"
        };
        res.json(result);
    } catch (error) {
        // 发生错误时回滚事务
        await db.rollback(connection);
        console.error("更新客户失败:", error); // 记录详细错误日志
        result = {
            code: 500,
            msg: "服务器错误，更新客户失败"
        };
        res.json(result);
    } finally {
        // 释放数据库连接
        connection.release();
    }
});

module.exports = router